﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;

namespace DAO
{
    public static class NguoiDAO
    {
        public static List<NguoiDTO> LoadAll()
        {
            List<NguoiDTO> result = new List<NguoiDTO>();
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_SelectAllNguoi", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                SqlDataReader reader;
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    NguoiDTO tmp = new NguoiDTO();
                    tmp.ID = reader.GetInt32(0);
                    tmp.Ten = reader.GetString(1);
                    tmp.TinhTrang = reader.GetBoolean(2);
                    result.Add(tmp);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }
        public static List<NguoiDTO> LoadCurrentOnly()
        {
            List<NguoiDTO> result = new List<NguoiDTO>();
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_SelectNguoiByTinhTrang", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@tinhTrang", SqlDbType.Bit).Value = 0;

                connection.Open();
                SqlDataReader reader;
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    NguoiDTO tmp = new NguoiDTO();
                    tmp.ID = reader.GetInt32(0);
                    tmp.Ten = reader.GetString(1);
                    tmp.TinhTrang = reader.GetBoolean(2);
                    result.Add(tmp);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }
        public static List<NguoiDTO> LoadExOnly()
        {
            List<NguoiDTO> result = new List<NguoiDTO>();
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_SelectNguoiByTinhTrang", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@tinhTrang", SqlDbType.Bit).Value = 1;

                connection.Open();
                SqlDataReader reader;
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    NguoiDTO tmp = new NguoiDTO();
                    tmp.ID = reader.GetInt32(0);
                    tmp.Ten = reader.GetString(1);
                    tmp.TinhTrang = reader.GetBoolean(2);
                    result.Add(tmp);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }
        public static bool Add(NguoiDTO nguoiDTO)
        {
            bool result = false;
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_InsertNguoi", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ten", SqlDbType.NVarChar).Value = nguoiDTO.Ten;
                command.Parameters.Add("@tinhTrang", SqlDbType.Bit).Value = nguoiDTO.TinhTrang;

                connection.Open();

                if (command.ExecuteNonQuery() != 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }
        public static bool Update(NguoiDTO nguoiDTO)
        {
            bool result = false;
            SqlConnection connection = new SqlConnection(DataProvider.ConnectionSting);
            try
            {
                SqlCommand command = new SqlCommand("sp_UpdateNguoi", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@id", SqlDbType.Int).Value = nguoiDTO.ID;
                command.Parameters.Add("@ten", SqlDbType.NVarChar).Value = nguoiDTO.Ten;
                command.Parameters.Add("@tinhTrang", SqlDbType.Bit).Value = nguoiDTO.TinhTrang;

                connection.Open();

                if (command.ExecuteNonQuery() != 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return result;
        }
    }
}
